--roznica miedzy instert a update
--sql4.freemysqlhosting.net 	

Your account number is: 41094 
Your new database is now ready to use. To connect to your database use these details 
Host: sql4.freemysqlhosting.net 
Database name: sql426970 
Database user: sql426970 
Database password: lF4%hL1* Port number: 3306




if exists(select 1 from master.dbo.sysdatabases where name = 'bazaIO') drop database bazaIO
GO
CREATE DATABASE bazaIO
GO

USE bazaIO;
GO

---------------------------------- PRODUCT
Create table Product (
idProduct int AUTO_INCREMENT PRIMARY KEY,
name varchar(20) not null,
price double precision,
category varchar(20) not null,
descriptionn varchar(20) not null
);
GO

Create table Pictures (
idProduct int,
link varchar(100),
CONSTRAINT FKPictureProduct FOREIGN KEY (idProduct) REFERENCES Product (idProduct)
);


Create table Details (
idProduct int ,
keyy varchar(20) not null,
value  varchar(100) not null,
CONSTRAINT FKDetailsProduct FOREIGN KEY (idProduct) REFERENCES Product (idProduct)
);
GO
------------------------------------/PRODUCT
--
--
------------------------------------MAGAZINE
Create table Magazine (
idProduct int ,
quantity int,
CONSTRAINT FKMagazineProduct FOREIGN KEY (idProduct) REFERENCES Product (idProduct)
);
GO
------------------------------------/MAGAZINE
--
--
------------------------------------USERR
Create table Userr (
idUserr int AUTO_INCREMENT PRIMARY KEY,
loginn varchar(20) not null,
passwordd varchar(20) not null,
userrName varchar(20) not null,
firstName varchar(20) not null,
lastName varchar(20) not null,
licenseNumber varchar(20) not null,
userrTYPE varchar(20) not null,
nip varchar(20) not null,
regon varchar(20) not null,
CONSTRAINT check_userrtype check ( userrType in ('SALESMAN','CUSTOMER','MANAGER'))
);


Create table Addres (
idUserr int,
city varchar(20) ,
street varchar(20) ,
houseNo varchar(20) ,
flatNO varchar(20) ,
postalCode varchar(20) , 
CONSTRAINT FKAddresUserr FOREIGN KEY (idUserr) REFERENCES Userr (idUserr)
);
GO

Create table UserrPermission (
idUserr int,
permission varchar(20) not null,
CONSTRAINT check_permission check ( permission in ('EDIT_ACCOUNTS','EDIT_PRODUCTS','MAKE_ORDERS','CANCLE_ORDERS')),
CONSTRAINT FKUserrPermissionUserr FOREIGN KEY (idUserr) REFERENCES Userr (idUserr)
);

------------------------------------/USERR
--
--
------------------------------------INVOICE
Create table Invoice (
number int AUTO_INCREMENT PRIMARY KEY,
idUserr int,
invoiceType varchar(20) not null,
paymentMethod varchar(20) not null,
price double precision,
creationDate date,
sellDate date,
deadlineDate date,
CONSTRAINT FKInvoiceUserr FOREIGN KEY (idUserr) REFERENCES Userr (idUserr),
CONSTRAINT check_paymentMethod check ( paymentMethod in ('CASH','TRANSFER')),
CONSTRAINT check_invoiceType check ( invoiceType in ('VAT','WZ'))
);


Create table Products (
number int,
idProduct int,
amount int,
CONSTRAINT FKProductsInvoice FOREIGN KEY (number) REFERENCES Invoice (number),
CONSTRAINT FKProductsProduct FOREIGN KEY (idProduct) REFERENCES Product (idProduct)
);
------------------------------------/INVOICE
--
--
------------------------------------ORDERR
Create table Orderr (
numberOrderr int AUTO_INCREMENT PRIMARY KEY,
number int,
CONSTRAINT FKOrderrInvoice FOREIGN KEY (number) REFERENCES Invoice (number)
);


Create table Properties (
numberOrderr int,
options varchar(20) not null,
advancedOptions varchar(20) not null,
CONSTRAINT FKPropertiesOrderr FOREIGN KEY (numberOrderr) REFERENCES Orderr (numberOrderr)
);
------------------------------------/ORDERR
--
--
------------------------------------STATISTIC
Create table UserrStatistic (
StatUdate date,
StatUamount int
);
GO

Create table OrderStatistic (
StatOdate date,
StatOamount int
);
GO

Create table ProductStatistic (
idProduct int,
StatPdate date,
StatPamount int,
CONSTRAINT FKProductStatisticProduct FOREIGN KEY (idProduct) REFERENCES Product (idProduct)
);
GO
--
--
------------------------------------INSERT
INSERT INTO Product (name,price,category,descriptionn) VALUES ('lozko','3030.50','Mebele do sypialni','Do spania');
INSERT INTO Product (name,price,category,descriptionn) VALUES ('lozko wodne','10500','Mebele sypialni','Do spania i zabawy');
INSERT INTO Product (name,price,category,descriptionn) VALUES ('szafka','5000','Mebele','szafka 2-drzwiowa');
INSERT INTO Product (name,price,category,descriptionn) VALUES ('drzwi','1200','Drzwi domowe','Drzwi drewniane');
INSERT INTO Product (name,price,category,descriptionn) VALUES ('biurko','800.50','Mebele do biura','kolor ciemny braz');
--
INSERT INTO Pictures (idProduct,link) VALUES (1,'http://image.ceneo.pl/data/products/10206179/i-signal-lozko-toronto.jpg');
INSERT INTO Pictures (idProduct,link) VALUES (2,'http://niespodzianka.pl/presents/53c79f6211f8f2afe3d694dd4792b4ab.jpg');
INSERT INTO Pictures (idProduct,link) VALUES (2,'http://niespodzianka.pl/presents/53c79f6211f8f2afe3d694dd4792b4ab.jpg');
INSERT INTO Pictures (idProduct,link) VALUES (3,'http://domirodzina.sklep.pl/385-1060-thickbox/szafa-dwudrzwiowa-vs-2-d.jpg');
INSERT INTO Pictures (idProduct,link) VALUES (3,'http://www.sklep.resmar.pl/upload/sklep_galeria/96/elba-prosta-elegancka-pojemna-szafa-dwudrzwiowa-w-kolorze-sosna-laredo-ciemne-els82.jpg');
INSERT INTO Pictures (idProduct,link) VALUES (4,'http://www.schody.info/drzwi_drewniane/drzwi15b.jpg');
INSERT INTO Pictures (idProduct,link) VALUES (5,'http://www.agatameble.pl/files/Jongtay/biurko.jpg');
--
INSERT INTO Details (idProduct,keyy,value) VALUES ('1','Wielkosc 100x100','Skorzana');
INSERT INTO Details (idProduct,keyy,value) VALUES ('2','Wielkosc 50x50','W srodku woda');
INSERT INTO Details (idProduct,keyy,value) VALUES ('3','Drewniana','Machon');
INSERT INTO Details (idProduct,keyy,value) VALUES ('4','Drewno, braz','Dab');
INSERT INTO Details (idProduct,keyy,value) VALUES ('5','Drewno, ciemny braz','Dab');

--
INSERT INTO Magazine (idProduct,quantity) VALUES (1,15);
INSERT INTO Magazine (idProduct,quantity) VALUES (2,5);
INSERT INTO Magazine (idProduct,quantity) VALUES (3,12);
INSERT INTO Magazine (idProduct,quantity) VALUES (4,6);
INSERT INTO Magazine (idProduct,quantity) VALUES (5,10);

--
INSERT INTO Userr (loginn,passwordd,userrName,firstName,lastName,licenseNumber,userrTYPE,nip,regon) VALUES ('?','?','?','?','?','?','?','?','?');
INSERT INTO Userr (loginn,passwordd,userrName,firstName,lastName,licenseNumber,userrTYPE,nip,regon) VALUES ('login1','pass1','username1','FirstName1','LastName1','licenseNr1','SALESMAN','1111111','1111111');
INSERT INTO Userr (loginn,passwordd,userrName,firstName,lastName,licenseNumber,userrTYPE,nip,regon) VALUES ('login2','pass2','username2','FirstName2','LastName2','licenseNr2','CUSTOMER','2222222','2222222');
INSERT INTO Userr (loginn,passwordd,userrName,firstName,lastName,licenseNumber,userrTYPE,nip,regon) VALUES ('login3','pass3','username3','FirstName3','LastName3','licenseNr3','MANAGER','3333333','3333333');
INSERT INTO Userr (loginn,passwordd,userrName,firstName,lastName,licenseNumber,userrTYPE,nip,regon) VALUES ('login4','pass4','username4','FirstName4','LastName4','licenseNr4','CUSTOMER','4444444','4444444');
INSERT INTO Userr (loginn,passwordd,userrName,firstName,lastName,licenseNumber,userrTYPE,nip,regon) VALUES ('login5','pass5','username5','FirstName5','LastName5','licenseNr5','SALESMAN','5555555','5555555');
INSERT INTO Userr (loginn,passwordd,userrName,firstName,lastName,licenseNumber,userrTYPE,nip,regon) VALUES ('login6','pass6','username6','FirstName6','LastName6','licenseNr6','MANAGER','6666666','6666666');
INSERT INTO Userr (loginn,passwordd,userrName,firstName,lastName,licenseNumber,userrTYPE,nip,regon) VALUES ('login7','pass7','username7','FirstName7','LastName7','licenseNr7','SALESMAN','7777777','7777777');
INSERT INTO Userr (loginn,passwordd,userrName,firstName,lastName,licenseNumber,userrTYPE,nip,regon) VALUES ('login8','pass8','username8','FirstName8','LastName8','licenseNr8','CUSTOMER','8888888','8888888');
--
INSERT INTO Addres (idUserr,city,street,houseNo,flatNO,postalCode) VALUES ('?','?','?','?','?','?');
INSERT INTO Addres (idUserr,city,street,houseNo,flatNO,postalCode) VALUES (1,'city1','street1','homenr1','flatno1','postC1');
INSERT INTO Addres (idUserr,city,street,houseNo,flatNO,postalCode) VALUES (2,'city2','street2','homenr2','flatno2','postC2');
INSERT INTO Addres (idUserr,city,street,houseNo,flatNO,postalCode) VALUES (3,'city3','street3','homenr3','flatno3','postC3');
INSERT INTO Addres (idUserr,city,street,houseNo,flatNO,postalCode) VALUES (4,null,null,null,null,null);
INSERT INTO Addres (idUserr,city,street,houseNo,flatNO,postalCode) VALUES (5,'city1','street1','homenr1','flatno1','postC1');
INSERT INTO Addres (idUserr,city,street,houseNo,flatNO,postalCode) VALUES (6,'city6','street6','homenr6','flatno6','postC6');
INSERT INTO Addres (idUserr,city,street,houseNo,flatNO,postalCode) VALUES (7,'city7','street7','homenr7','flatno7','postC7');
INSERT INTO Addres (idUserr,city,street,houseNo,flatNO,postalCode) VALUES (8,null,null,null,null,null);
--
INSERT INTO UserrPermission (idUserr,permission) VALUES ('?','?');
INSERT INTO UserrPermission (idUserr,permission) VALUES (1,'EDIT_ACCOUNTS');
INSERT INTO UserrPermission (idUserr,permission) VALUES (2,'MAKE_ORDERS');
INSERT INTO UserrPermission (idUserr,permission) VALUES (3,'EDIT_ACCOUNTS');
INSERT INTO UserrPermission (idUserr,permission) VALUES (3,'EDIT_PRODUCTS');
INSERT INTO UserrPermission (idUserr,permission) VALUES (3,'CANCLE_ORDERS');
INSERT INTO UserrPermission (idUserr,permission) VALUES (4,'MAKE_ORDERS');
INSERT INTO UserrPermission (idUserr,permission) VALUES (4,'CANCLE_ORDERS');
INSERT INTO UserrPermission (idUserr,permission) VALUES (5,'EDIT_ACCOUNTS');
INSERT INTO UserrPermission (idUserr,permission) VALUES (6,'EDIT_ACCOUNTS');
INSERT INTO UserrPermission (idUserr,permission) VALUES (7,'EDIT_ACCOUNTS');
INSERT INTO UserrPermission (idUserr,permission) VALUES (8,'CANCLE_ORDERS');
INSERT INTO UserrPermission (idUserr,permission) VALUES (8,'MAKE_ORDERS'); --2 4 8
--
INSERT INTO Invoice (idUserr,invoiceType,paymentMethod,price,creationDate,sellDate,deadlineDate) VALUES ('?','?','?','?','?','?','?');
INSERT INTO Invoice (idUserr,invoiceType,paymentMethod,price,creationDate,sellDate,deadlineDate) VALUES (2,'VAT','CASH',50000,'2013-12-28','2013-12-29','2013-12-29');
INSERT INTO Invoice (idUserr,invoiceType,paymentMethod,price,creationDate,sellDate,deadlineDate) VALUES (4,'WZ','CASH',6000,'2014-01-18','2014-01-19',null);
INSERT INTO Invoice (idUserr,invoiceType,paymentMethod,price,creationDate,sellDate,deadlineDate) VALUES (8,'VAT','CASH',1000,'2013-11-11','2013-11-12','2013-11-12');
INSERT INTO Invoice (idUserr,invoiceType,paymentMethod,price,creationDate,sellDate,deadlineDate) VALUES (4,'VAT','TRANSFER',1500,'2013-12-28',null,null);
--
INSERT INTO Products (number,idProduct,amount) VALUES ('?','?','?');
INSERT INTO Products (number,idProduct,amount) VALUES (1,2,7);
INSERT INTO Products (number,idProduct,amount) VALUES (2,1,2);
INSERT INTO Products (number,idProduct,amount) VALUES (3,4,1);
INSERT INTO Products (number,idProduct,amount) VALUES (4,3,1);
--
INSERT INTO Orderr (number) VALUES ('?');
INSERT INTO Orderr (number) VALUES (1);
INSERT INTO Orderr (number) VALUES (2);
INSERT INTO Orderr (number) VALUES (5);
INSERT INTO Orderr (number) VALUES (6);
--
INSERT INTO Properties (numberOrderr,options,advancedOptions) VALUES ('?','?','?');
INSERT INTO Properties (numberOrderr,options,advancedOptions) VALUES (1,'option1','adoption1');
INSERT INTO Properties (numberOrderr,options,advancedOptions) VALUES (2,'option2','adoption2');
INSERT INTO Properties (numberOrderr,options,advancedOptions) VALUES (3,'option3','adoption3');
INSERT INTO Properties (numberOrderr,options,advancedOptions) VALUES (4,'option4','adoption4');
------------------------------------/INSERT
--
--
------------------------------------UPDTAE
UPDATE Userr set loginn=?, userrName=?, firstName=?, lastName=?, licenseNumber=?, userrTYPE=?, nip=?, regon=?, passwordd=? where idUserr=1;
--
UPDATE Product set name=?, descriptionn=?,price=?,category=?  where idProduct=?;
--
UPDATE Userr set loginn=?, userrName=?, firstName=?, lastName=?, licenseNumber=? userrTYPE=?, nip=?, regon=? where idUserr=?;
--
UPDATE Addres set city=?, street=?, houseNo=?, flatNO=?, postalCode=? where idUserr=?;
--
UPDATE Magazine set quantity=?  where idProduct=3;
--
UPDATE Invoice set idUserr=?, invoiceType=?, paymentMethod=?, price=?, creationDate='2013-12-26', sellDate=?, deadlineDate=? where number=?; 
UPDATE Invoice set creationDate='2013-12-26' where number=5; 
--
UPDATE Orderr set number=? where numberOrderr=?;
-------------------------------------UPDATE W INNY SPOSOD dla poszczegolnych tabel
DELETE FROM Magazine WHERE idProduct=? ;
INSERT INTO Magazine (idProduct,quantity) VALUES (?,?);
--
DELETE FROM Details WHERE idProduct= ?;
INSERT INTO Details (idProduct,keyy,value) VALUES (?,?,?);
--
DELETE FROM UserrPermission WHERE idUserr= ?;
INSERT INTO UserrPermission (idUserr,permission) VALUES ('?','?');
--
DELETE FROM Products WHERE number= ?;
INSERT INTO Products (number,idProduct,amount) VALUES (?,?,?);
--
DELETE FROM Properties WHERE numberOrderr=?
INSERT INTO Properties (numberOrderr,options,advancedOptions) VALUES (?,?,?);
------------------------------------/UPDATE
--
--
-------------------------------------DROP
drop table Product
drop table Pictures
drop table Details
drop table Magazine
drop table Addres
drop table Userr
drop table UserrPermission
drop table Products
drop table Invoice
drop table Orderr
drop table Properties 
-------------------------------------/DROP
--
--
-------------------------------------SELECT
select * from Product;
select * from Pictures;
select * from Details;
select * from Addres;
select * from Userr;
select * from UserrPermission;
select * from Invoice;
select * from Products;
select * from Orderr;
select * from Properties;
-------------------------------------SELECT po ID
select * from Product where idProduct= ?;
select * from Pictures where idProduct= ?;
select * from Details where idProduct= ?;
select * from Magazine where idProduct= ?;
select * from Addres where idUserr= ?;
select * from Userr where idUserr= ?;
select * from UserrPermission where idUserr= ?;
select * from Invoice where number= ?;
select * from Products where number= ?;
select * from Orderr where numberOrderr=?;
select * from Properties where numberOrderr=?;
-------------------------------------/SELECT

--getUserOrder
select numberOrderr, Orderr.number from Orderr 
join Invoice on Orderr.number=Invoice.number and idUserr=(select idUserr from Userr where idUserr=3)
--lub
select * from Orderr 
where Orderr.number=ANY(select Invoice.number from Invoice 
where  idUserr=(select idUserr from Userr where idUserr=3))

--getUserOrderHisotry
select numberOrderr, Orderr.number from Orderr 
join Invoice on Orderr.number=Invoice.number and Invoice.creationDate > '2013-12-26' and Invoice.creationDate <'2013-12-30' and idUserr=(select idUserr from Userr where idUserr=3)
--lub
select * from Orderr 
where Orderr.number=ANY(select Invoice.number from Invoice 
where Invoice.creationDate > '2013-12-26' and Invoice.creationDate <'2013-12-30' and
idUserr=(select idUserr from Userr where idUserr=3))

-- GetUsersProduct
select name from Product 
join Products on Product.idProduct=Products.idProduct
join Orderr on Products.number=Orderr.number and 
numberOrderr=(select numberOrderr from Orderr where numberOrderr=2)
--lub
Select name from Product where Product.idProduct=
(select Products.idProduct from Products, Orderr where Products.number=Orderr.number and numberOrderr=
(select numberOrderr from Orderr where numberOrderr=2))

--getUserOrderProducts
select Products.idProduct, amount from Products 
join Product on Product.idProduct=Products.idProduct and Products.number=(select number from Orderr where numberOrderr=2)
--lub 
Select Products.idProduct , amount from Products, Product 
where Products.idProduct=Product.idProduct and Products.number=(select number from Orderr where numberOrderr=2)



Select SUM(Product.price) as cena from Product,Products,Invoice, Orderr where 
Product.idProduct = Products.idProduct and
Products.number = Invoice.number and
Invoice.number = Orderr.number and orderr.numberOrderr=1;
